package dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import pojo.PageTools;
import pojo.Student;
import pojo.User;

import java.sql.SQLException;
import java.util.List;

public class Dao {
    private QueryRunner qr = new QueryRunner(new ComboPooledDataSource());

    //查询总条数
//    public int queryAllSize() {
//        String sql = "select count(*) from student";
//        try {
//           long l = (long) qr.query(sql,new ScalarHandler());
//           return (int) l;
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        return 0;
//    }

//    public List<Student> dimQueryStudent(PageTools pt) {
//        String sql = "select * from student limit ?,?";
//        try {
//           return qr.query(sql,new BeanListHandler<>(Student.class),pt.getIndex(),pt.getPageSize());
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//        return null;
//    }
//    总条数查询数据库
    public int dimQueryCountStudent(String sname, String sex) {
        String sql = "select count(*) from student where 1 = 1 ";
        if(sname != null && !"".equals(sname)) {
            sql += " and sname like '%" + sname + "%' ";
        }
        if(sex != null && !"".equals(sex)) {
            sql += " and sex = '" + sex + "'";
        }

        try {
            long l = (long)qr.query(sql,new ScalarHandler());
            return (int)l;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
// 模糊搜索
    public List<Student> dimQueryStudent(String sname, String sex, PageTools pt) {
        /**
         * 情况
         * 1、没有传递sname，没有传sex
         * select * from student
         * 2、传递了sname，没有传递sex
         * select * from student where sname like '%sname%'
         * 3、没有传递sname，传递了sex
         * select * from student where sex = sex
         * 4、传递了sname，传递了sex
         * select * from student where sname like '%sname%' and sex = sex
         */
        String sql = "select * from student where 1 = 1 ";
        if(sname != null && !"".equals(sname)) {
            sql += " and  sname like '%" + sname + "%'";
        }

        //传递了sex
        if(sex != null && !"".equals(sex)) {
            sql += "  and sex = '" + sex + "'";
        }
        sql += "  limit ?,?";
        try {
          return   qr.query(sql,new BeanListHandler<>(Student.class),pt.getIndex(),pt.getPageSize());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public int selectUser(String username, String password) {
        String sql = "insert into user values (?,?)";
        Object[] arr ={username,password};
        try {
        return     qr.update(sql,arr);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
